Index Wars

Le pouvoir de Jedi des Index dans l'univers de Postgres

Forum PHP
2023-10-13

Image by Sebastian from Pixabay
logo EDB

Who am I

Image by Anemone123 from Pixabay

Indexes

  • Quoi?
  • Quels types?
  • Comment les utiliser?
logo EDB

Qu'est-ce qu'un index

  • Référence pour un accès rapide
  • N'est pas gratuit
Image by Max from Pixabay

Les bases

CREATE INDEX [ name ] ON table_name
  ( { ( column_name [, ...] ) | ( expression ) }
  [ ASC | DESC ]
  [ NULLS { FIRST | LAST } ]) 
DROP INDEX name
  [ CASCADE | RESTRICT ] 

Quelques exemples

CREATE INDEX test1_id_index ON test1 (id)
DROP INDEX title_idx
Image by Pexels from Pixabay

Quelques exemples

CREATE INDEX test1_id_index ON test1 (id)
SELECT id FROM test1 WHERE id=5 ✅
SELECT id FROM test1 ❌
SELECT id FROM test1 WHERE fact(id)=120 ❌
Image by Pexels from Pixabay

Combiner différents index

SELECT * FROM mytable WHERE a = 5 AND b = 6
  1. Scan de l'index sur a
  2. Scan de l'index sur b
  3. Ajout d'un AND sur les résultats

Attention à ORDER BY

Image by Andrew Martin from Pixabay

Index sur plusieurs colonnes

CREATE INDEX ON mytable (a,b);
SELECT * FROM mytable WHERE a = 5 AND b = 6 ✅
SELECT * FROM mytable WHERE a = 5 ✅
SELECT * FROM mytable WHERE b = 6 ❌
Image by Andrew Martin from Pixabay

Index sur des expressions

CREATE INDEX ON  mytable (lower(a))
SELECT * FROM mytable WHERE lower(a) = 'value' ✅
SELECT * FROM mytable WHERE a = upper('value') ❌
SELECT * FROM mytable WHERE a = 'value' ❌
Image by Andrew Martin from Pixabay

Index partiels

CREATE INDEX ON  mytable (a) WHERE a IS NULL
SELECT * FROM mytable WHERE a IS NULL ✅
SELECT * FROM mytable WHERE a = 5 ❌
Image by tookapic from Pixabay

Index couvrants

CREATE INDEX ON  mytable (a) INCLUDE (b)
SELECT a,b FROM mytable WHERE a = 5 ✅
SELECT a,b FROM mytable WHERE a = 5 AND b = 6 ❌
Image by ErikaWittlieb from Pixabay

Les opérateurs de comparaison

  • = < > <= >=
  • LIKE ILIKE ~ ~* SIMILAR TO
  • && ||
  • @> <@
  • &< &> &<| |&> << >> ~= <<| |>>
Image by patrykdzido from Pixabay

Les types d'index

B-tree Hash BRIN GIN
=
< <= >= >

Les types d'index

GiST SP-GiST GIN
<@ >@
&&
<< >> ~= <<| |>>
&< &> &<| |&>
Image by Mátyás Varga from Pixabay

Les index des extensions

  • Bloom
  • Vodka
  • Rum
  • Hierarchical Navigable Small Worlds (HNSW)
Image by tookapic from Pixabay

Les types d'index

CREATE INDEX name ON table (column ) USING method
CREATE INDEX pointloc ON points USING gist (box(location,location))
CREATE INDEX gin_idx ON documents_table USING GIN (locations)
Image by Piotr Majewski from Pixabay

Les classes d'opérateur

CREATE INDEX name ON table (column opclass [ ( opclass_options ) ] )
  • text_pattern_ops
  • varchar_pattern_ops
  • bpchar_pattern_ops

Utilisé pour la recherche de pattern (régular expressions, sous-chaînes de caractères...)

Tri

CREATE INDEX name ON table (column)
  [ COLLATE collation ]
  [ ASC | DESC ]
  [ NULLS { FIRST | LAST } ]
  • Le tri dépend de la collation
  • Choisissez l'ordre de votre choix
  • Choisissez ce que vous voulez faire des valeurs NULL

NULL values

CREATE INDEX name ON table (column) [ NULLS [ NOT ] DISTINCT ] 
Image by Freepik

Maintenance

  • ANALYZE
  • autovacuum
  • default_statistics_target

Using an index

  • Dépendant des statistiques
  • On ne peut pas le forcer
  • Arrêtez d'être des control freaks!
Image by Piotr Zakrzewski from Pixabay§:w

Explain plans

EXPLAIN [ ( option [, ...] ) ] statement
where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

Explain plans

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)
Image by Sasin Tipchai from Pixabay

Usage

\d pg_stat_all_indexes
                   View "pg_catalog.pg_stat_all_indexes"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 relid         | oid                      |           |          | 
 indexrelid    | oid                      |           |          | 
 schemaname    | name                     |           |          | 
 relname       | name                     |           |          | 
 indexrelname  | name                     |           |          | 
 idx_scan      | bigint                   |           |          | 
 last_idx_scan | timestamp with time zone |           |          | 
 idx_tup_read  | bigint                   |           |          | 
 idx_tup_fetch | bigint                   |           |          | 
Image by Lukas from Pixabay

Attention

  • Index en doublon
  • Index inutilisés
  • Index manquants
Image by Andrew Martin from Pixabay

Some scripts

https://github.com/pgexperts/pgx_scripts

QR code to the scripts
Image by René Bittner from Pixabay

Use the index, Luke!

https://use-the-index-luke.com/

QR code to the scripts
Image by eezy from Pixabay

Fin

  • Gérer les index n'est pas facile
  • Personne n'est mort en lisant la documentation!
  • Je vous ai juste donné des mots clés pour que vous alliez chercher
Image by Andrew Martin from Pixabay